const Router = require('koa-router');
const router = new Router();
const tool = require('../../tools/tool')
const {db, QueryTypes} = require("../../models/db");

// 应收款汇总
router.post('/receipt/yskhz', async ctx => {
    let user = ctx.user
    let body = ctx.request.body
    let formData = body.formData
    let tenantId = user.tenantId;
    let beginDate = formData.beginDate ? formData.beginDate: '2001-01-01';
    let endDate = formData.endDate?formData.endDate:tool.getDay();
    // let contactCatId = formData.contactCatId?formData.contactCatId:0;
    let buId = formData.buId?formData.buId:0;
    let salesId = formData.salesId?formData.salesId:0;
    let excel = body.excel
    let sqls = `
        with bills as (
            select tenantId,buId,buName,transTypeName,billDate,
                   iif(billType='RECEIPT',0,arrearsReal) ys,
                   iif(billType='RECEIPT',totalAmount,0) sk
            from v_main where tenantId=${tenantId} and billType in('SALE','YS','RECEIPT') and billState='accounted' and billDate<='${endDate}'
                        and (${buId}=0 or buId=${buId}) 
                        and (${salesId}=0 or salesId=${salesId}) 
            union all
            select tenantId,buId,buName,N'凭证' transTypeName,billDate,
                  iif(price1<>0,price1,0) ys,
                  iif(price2<>0,price2,0) sk
            from v_card_info where tenantId=${tenantId} and billState='accounted'
            and subjectId in (select id from category where tenantId=${tenantId} and name in(N'应收账款',N'其他应收款'))
            and byIid=0 and billDate<='${endDate}'
            and (${buId}=0 or buId=${buId})
        ) 
        select tenantId, buId, buName,sum(initYe) initYe,sum(ys) ys,sum(sk) sk from 
        (
            select  tenantId, buId,buName, iif(billDate<'${beginDate}',ys-sk, 0) initYe, iif(billDate<'${beginDate}',0, ys) ys, iif(billDate<'${beginDate}',0, sk) sk from bills
        ) a group by tenantId, buId, buName
    `
    let list = await db.query(sqls, {type: QueryTypes.SELECT})
    list.map(row => {
        row.ye = tool.minus(tool.add(row.initYe,row.ys),row.sk)
    })
    if(excel) {
        return tool.doExcelOut(ctx, list)
    }
    else {
        list.map(row => {
            row.beginDate = beginDate
            row.endDate = endDate
        })
        return ctx.suc('', {
            total: list.length,
            list: list
        })
    }
})

module.exports = router;
